Hi i want to create a package in ssis,in that package i want to use excel file as source.in that file name like test_08_03_2012(like today's date)so i want to use that file for load ..this file name change tomorrow like test_08_04_2012 how to take this as source file dynamically

In the excel connection manager , write an expression like "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+"C:\\Users\\anil\\Desktop\\"+"abc*.xlsx;Extended Properties="+"\""+"Excel 12.0;HDR=YES"+"\""+";" This will do in your case.. put test*.xlsx

In the excel connection manager , write an expression like "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+"C:\\Users\\anil\\Desktop\\"+"abc*.xlsx;Extended Properties="+"\""+"Excel 12.0;HDR=YES"+"\""+";" This will do in your case... ya thanks for ur reply.if i use ur expression means , test_08_03_2012, test_08_04_2012 both file are in same source folder it will take which data

I am no sure, if you want to piack a file from current date i can give you a solution.. try this.. "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+"C:\\Users\\anil\\Desktop\\"+"abc_"+(LEN((DT_WSTR,2)DAY(GETDATE())) == 1 ? "0" + (DT_WSTR,2)DAY(GETDATE()) : (DT_WSTR,2)DAY(GETDATE()))+"_"+(LEN((DT_WSTR,2)MONTH(GETDATE())) == 1 ? "0" + (DT_WSTR,2)MONTH(GETDATE()) : (DT_WSTR,2)MONTH(GETDATE()))+"_"+((DT_WSTR,4)YEAR(GETDATE()))+".xlsx;Extended Properties="+"\""+"Excel 12.0;HDR=YES"+"\""+";"

Here are the steps you can do to get the latest file from the source folder and process it: 1 - Add 2 variables "@FileName" string, "@FileDate" datetime to the package. 2 - set @FileDate value to 1/1/1900. 3 - Add a "Foreach Container" to get the list files from the source folder 4 - Within that "Foreach Container", add a "Script Task". 5 - In this script task, split the file name to get the date from the file name and check if the date from the file name is greater than @FileDate. If it is then save the file name to @FileName and save the date to @FileDate. pseudo code will be: var <filename> = <get file name> var <date> = <build date from file name> if <date> GREATER THAN @FileDate SET @FileName = <Filename> SET @FileDate = <date> 6 -Save the script task 7 - At the end of loop you will have the latest file name. Hope this helps- Please mark the post as answered if it answers your question

Here are the steps you can do to get the latest file from the source folder and process it: 1 - Add 2 variables "@FileName" string, "@FileDate" datetime to the package. 2 - set @FileDate value to 1/1/1900. 3 - Add a "Foreach Container" to get the list files from the source folder 4 - Within that "Foreach Container", add a "Script Task". 5 - In this script task, split the file name to get the date from the file name and check if the date from the file name is greater than @FileDate. If it is then save the file name to @FileName and save the date to @FileDate. pseudo code will be: var <filename> = <get file name> var <date> = <build date from file name> if <date> GREATER THAN @FileDate SET @FileName = <Filename> SET @FileDate = <date> 6 -Save the script task 7 - At the end of loop you will have the latest file name. Hope this helps- Please mark the post as answered if it answers your question

You must be having some business logic to set the filename daily. Use that business logic to set a variable value in SSIS and then use that variable to dynamically set the source file name of a excel file manager. Follow this for more information on how to use a variable to dynamically set a source file name.http://btsbee.wordpress.com/